import pymysql
from typing import List, Dict, Any, Optional, Tuple

class MySQLDatabase:
    def __init__(
        self,
        host: str,
        user: str,
        password: str,
        database: str,
        port: int = 3306,
        charset: str = "utf8mb4"
    ):
        """
        初始化数据库连接参数
        :param host: 数据库地址
        :param user: 用户名
        :param password: 密码
        :param database: 数据库名称
        :param port: 端口号，默认3306
        :param charset: 字符编码，默认utf8mb4
        """
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.connection = None
        self.cursor = None

    def connect(self) -> None:
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port,
                charset=self.charset,
                cursorclass=pymysql.cursors.DictCursor  # 返回字典格式的结果
            )
            self.cursor = self.connection.cursor()
        except pymysql.Error as e:
            raise ConnectionError(f"数据库连接失败: {e}")

    def disconnect(self) -> None:
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

    def execute_query(
        self,
        sql: str,
        params: Optional[tuple] = None
    ) -> List[Dict[str, Any]]:
        """
        执行查询语句
        :param sql: SQL语句
        :param params: 查询参数
        :return: 查询结果列表（字典格式）
        """
        try:
            self.cursor.execute(sql, params or ())
            return self.cursor.fetchall()
        except pymysql.Error as e:
            self.connection.rollback()
            raise RuntimeError(f"查询执行失败: {e}")

    def execute_update(
        self,
        sql: str,
        params: Optional[tuple] = None
    ) -> int:
        """
        执行更新操作（INSERT/UPDATE/DELETE）
        :param sql: SQL语句
        :param params: 查询参数
        :return: 受影响的行数
        """
        try:
            affected_rows = self.cursor.execute(sql, params or ())
            self.connection.commit()
            return affected_rows
        except pymysql.Error as e:
            self.connection.rollback()
            raise RuntimeError(f"更新操作失败: {e}")

    def execute_many(
        self,
        sql: str,
        params: List[Tuple],
        batch_size: int = 1000
    ) -> int:
        """
        批量插入数据
        :param sql: INSERT语句模板
        :param params: 包含多个参数元组的列表
        :param batch_size: 每批插入的数量，默认1000
        :return: 总共插入的行数
        """
        if not params:
            return 0

        total_rows = 0
        try:
            # 分批处理
            for i in range(0, len(params), batch_size):
                batch_params = params[i:i+batch_size]
                affected = self.cursor.executemany(sql, batch_params)
                total_rows += affected
                # 每批提交一次（根据需求可选）
                self.connection.commit()
            return total_rows
        except pymysql.Error as e:
            self.connection.rollback()
            raise RuntimeError(f"批量插入失败: {e}")


    def begin_transaction(self) -> None:
        """开启事务"""
        self.connection.begin()

    def commit(self) -> None:
        """提交事务"""
        self.connection.commit()

    def rollback(self) -> None:
        """回滚事务"""
        self.connection.rollback()

    def __enter__(self):
        """支持上下文管理"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """退出上下文时自动关闭连接"""
        self.disconnect()

# 使用示例
if __name__ == "__main__":
    # 配置数据库信息
    db_config = {
        "host": "localhost",
        "user": "root",
        "password": "111111",
        "database": "gardevoir",
        "port": 3306
    }

    target_usernames = [
        "又一充电中",
        "梨安不迷路",
        "沐霂是MUMU呀",
        "恬豆发芽了"
    ]

    # 使用上下文管理器自动管理连接
    with MySQLDatabase(**db_config) as db:
        # 示例查询
        # 构造SQL查询
        sql = """
            SELECT * 
            FROM user 
            WHERE user_name IN ({})
        """.format(','.join(['%s'] * len(target_usernames)))

        # 执行查询
        results = db.execute_query(sql, tuple(target_usernames))
        print(f"查询到{len(results)}条记录")
        print(f":::{results}")

        # # 示例更新
        # affected = db.execute_update(
        #     "INSERT INTO users (name, age) VALUES (%s, %s)",
        #     ("Alice", 25)
        # )
        # print(f"插入{affected}条记录")

        # 事务示例
        try:
            db.begin_transaction()
            # 执行多个更新操作...
            db.commit()
        except Exception as e:
            db.rollback()
            print(f"事务执行失败: {e}")